package com.fjh.springboot_01.mapper;

import com.fjh.springboot_01.pojo.tutor.Tutoring;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface TutoringMapper {
    @Insert("INSERT INTO tutoring (teacher_id, title, description, subjects, target_level, " +
            "hourly_rate, teaching_method, location, available_time, status, create_time, update_time) " +
            "VALUES (#{teacherId}, #{title}, #{description}, #{subjects}, #{targetLevel}, " +
            "#{hourlyRate}, #{teachingMethod}, #{location}, #{availableTime}, #{status}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(Tutoring tutoring);

    @Select("SELECT t.*, u.username as teacher_name, u.user_pic as teacher_avatar, " +
            "ti.real_name as teacher_real_name, ti.education, ti.major, ti.school, ti.province " +
            "FROM tutoring t " +
            "LEFT JOIN user u ON t.teacher_id = u.id " +
            "LEFT JOIN teacher_info ti ON t.teacher_id = ti.user_id " +
            "WHERE t.status = 1 " +
            "ORDER BY t.create_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "teacher_avatar", property = "teacherAvatar"),
        @Result(column = "teacher_real_name", property = "teacherRealName")
    })
    List<Tutoring> findAllActiveWithTeacherInfo();

    @Select("SELECT t.*, u.username as teacher_name, u.user_pic as teacher_avatar " +
            "FROM tutoring t " +
            "LEFT JOIN user u ON t.teacher_id = u.id " +
            "WHERE t.status = 1 " +
            "ORDER BY t.create_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "teacher_avatar", property = "teacherAvatar")
    })
    List<Tutoring> findAllActive();

    @Select("SELECT t.*, u.username as teacher_name, u.user_pic as teacher_avatar " +
            "FROM tutoring t " +
            "LEFT JOIN user u ON t.teacher_id = u.id " +
            "WHERE t.teacher_id = #{teacherId} " +
            "ORDER BY t.create_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "teacher_avatar", property = "teacherAvatar")
    })
    List<Tutoring> findByTeacherId(Integer teacherId);

    @Select("SELECT t.*, u.username as teacher_name, u.user_pic as teacher_avatar " +
            "FROM tutoring t " +
            "LEFT JOIN user u ON t.teacher_id = u.id " +
            "WHERE t.id = #{id}")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "teacher_avatar", property = "teacherAvatar")
    })
    Tutoring findById(Integer id);

    @Update("UPDATE tutoring SET status = #{status}, update_time = NOW() WHERE id = #{id}")
    void updateStatus(@Param("id") Integer id, @Param("status") Integer status);

    @Update("UPDATE tutoring SET title = #{title}, description = #{description}, " +
            "subjects = #{subjects}, target_level = #{targetLevel}, hourly_rate = #{hourlyRate}, " +
            "teaching_method = #{teachingMethod}, location = #{location}, " +
            "available_time = #{availableTime}, update_time = NOW() " +
            "WHERE id = #{id}")
    void update(Tutoring tutoring);

    @Select("SELECT teacher_id FROM tutoring WHERE id = #{tutoringId}")
    Integer getTeacherIdByTutoringId(Integer tutoringId);
    
    @Select("SELECT t.*, u.username as teacher_name, u.user_pic as teacher_avatar " +
            "FROM tutoring t " +
            "LEFT JOIN user u ON t.teacher_id = u.id " +
            "WHERE t.status = 1 " +
            "ORDER BY t.create_time DESC LIMIT #{limit}")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "teacher_avatar", property = "teacherAvatar")
    })
    List<Tutoring> findLatest(int limit);
    
    @Select("SELECT subjects as subject, COUNT(*) as count " +
            "FROM tutoring " +
            "WHERE status = 1 " +
            "GROUP BY subjects")
    List<Map<String, Object>> getSubjectStatistics();
    
    @Select("SELECT COUNT(*) FROM tutoring")
    int getTotalCount();
    
    @Select("SELECT COUNT(*) FROM tutoring WHERE status = 2")
    int getCompletedCount();

    /**
     * 根据教师ID列表获取可用课程
     * @param teacherIds 教师ID列表
     * @return 课程列表
     */
    @Select("<script>" +
            "SELECT t.*, u.username as teacher_name, u.user_pic as teacher_avatar " +
            "FROM tutoring t " +
            "LEFT JOIN user u ON t.teacher_id = u.id " +
            "WHERE t.status = 1 AND t.teacher_id IN " +
            "<foreach collection='list' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " ORDER BY t.create_time DESC" +
            "</script>")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "teacher_avatar", property = "teacherAvatar")
    })
    List<Tutoring> findByTeacherIdsAndActive(List<Integer> teacherIds);
    
    /**
     * 查询所有家教信息（包括已下架），用于管理员审核
     */
    @Select("SELECT t.*, u.username as teacher_name, u.user_pic as teacher_avatar, " +
            "ti.real_name as teacher_real_name, ti.education, ti.major, ti.school, ti.province " +
            "FROM tutoring t " +
            "LEFT JOIN user u ON t.teacher_id = u.id " +
            "LEFT JOIN teacher_info ti ON t.teacher_id = ti.user_id " +
            "ORDER BY t.status ASC, t.create_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "teacher_avatar", property = "teacherAvatar"),
        @Result(column = "teacher_real_name", property = "teacherRealName"),
        @Result(column = "education", property = "education"),
        @Result(column = "major", property = "major"),
        @Result(column = "school", property = "school"),
        @Result(column = "province", property = "province")
    })
    List<Tutoring> findAll();
} 